#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into hive.edu_dm.wf_dm_cus_rollup
WITH temp AS (
    SELECT dt,
           substr(dt, 1, 7) AS year_month,
           substr(dt, 1, 4) AS year_code,
           origin_type,
           origin_channel,
           clue_state,
           area,
           center_name,
           school_id,
           school_name,
           subject_id,
           subject_name,
           group_type AS group_type_old,
           rel_cnt
    FROM hive.edu_dws.wf_dws_cus_day_cnt
),
rel_temp AS (
    SELECT dt,
           year_month,
           year_code,
           origin_type,
           origin_channel,
           clue_state,
           area,
           center_name,
           school_id,
           school_name,
           subject_id,
           subject_name,
           CASE
               WHEN grouping(dt) = 0 THEN 'day'
               WHEN grouping(year_month) = 0 THEN 'year_month'
               WHEN grouping(year_code) = 0 THEN 'year'
           END AS time_type,
           group_type_old,
           CASE
               WHEN grouping(subject_id) = 0 THEN 'sub'
               WHEN grouping(center_name) = 0 THEN 'center'
               WHEN grouping(school_id) = 0 THEN 'school'
               WHEN grouping(area) = 0 THEN 'area'
               WHEN grouping(origin_channel) = 0 THEN 'origin'
               ELSE 'all'
           END AS group_type_new,
           SUM(rel_cnt) AS rel_cnt
    FROM temp
    GROUP BY GROUPING SETS (
            -- Year
        (year_code, origin_type, clue_state,group_type_old),
        (year_code, origin_type, clue_state, origin_channel,group_type_old),
        (year_code, origin_type, clue_state, school_id, school_name,group_type_old),
        (year_code, origin_type, clue_state, subject_id, subject_name,group_type_old),
        (year_code, origin_type, clue_state, area,group_type_old),
        (year_code, origin_type, clue_state, center_name,group_type_old),
              -- Month
        (year_code,year_month, origin_type, clue_state,group_type_old),
        (year_code,year_month, origin_type, clue_state, origin_channel,group_type_old),
        (year_code,year_month, origin_type, clue_state, school_id, school_name,group_type_old),
        (year_code,year_month, origin_type, clue_state, subject_id, subject_name,group_type_old),
        (year_code,year_month, origin_type, clue_state, area,group_type_old),
        (year_code,year_month, origin_type, clue_state, center_name,group_type_old),

        -- Day
        (year_code,year_month,dt, origin_type, clue_state,group_type_old),
        (year_code,year_month,dt, origin_type, clue_state, origin_channel,group_type_old),
        (year_code,year_month,dt, origin_type, clue_state, school_id, school_name,group_type_old),
        (year_code,year_month,dt, origin_type, clue_state, subject_id, subject_name,group_type_old),
        (year_code,year_month,dt, origin_type, clue_state, area,group_type_old),
        (year_code,year_month,dt, origin_type, clue_state, center_name,group_type_old)

    )
)
SELECT dt,
       year_month,
       year_code,
       origin_type,
       origin_channel,
       clue_state,
       area,
       center_name,
       school_id,
       school_name,
       subject_id,
       subject_name,
       time_type,
       group_type_old,
       group_type_new,
       rel_cnt
FROM rel_temp
where group_type_old=group_type_new;






insert into hive.edu_dm.wf_dm_clu_hour
select dt,
       hr,
       clue_state,
       origin_type,
       clu_day_cnt,
       clu_hour_cnt,
       eff_hour_cnt,
       clu_hour_rate
from hive.edu_dws.wf_dws_clu_hour_cnt;"
